#!/usr/bin/env python3
# -*- coding:utf-8 -*-

from openpyxl import load_workbook
from openpyxl import Workbook
from copy import copy
from openpyxl.utils import get_column_letter,column_index_from_string
import os.path

def copy_sheet_simple(src_sheet,tag_sheet):
    ws = src_sheet
    ws2 = tag_sheet

    max_row = ws.max_row
    max_column = ws.max_column

    #先处理合并单元格
    wm = list(zip(ws.merged_cells))
    if len(wm) > 0:
        for i_mg in range(0,len(wm)):
            cell2=str(wm[i_mg]).replace('(<MergeCell ','').replace('>,)','')
            ws2.merge_cells(cell2)

    for i_row in range(1,max_row+1):
        ws2.row_dimensions[i_row].height = ws.row_dimensions[i_row].height  #设置相同行高
        for i_col in range(1,1 + max_column):
            # if i_col < 27:
            #     c =chr(i_col+64).upper()    #ASCII字符,chr(65)='A'
            # else:
            #     if i_col < 677:
            #         c = chr(divmod(i_col,26)[0]+64)+chr(divmod(i_col,26)[1]+64)
            #     else:
            #         c = chr(divmod(i_col,676)[0]+64) + chr(divmod(divmod(i_col,676)[1],26)[0]+64) + chr(divmod(divmod(i_col,676)[1],26)[1]+64)
            # i='%s%d'%(c,i_row)  #单元格编号
            i='%s%d'%(get_column_letter(i_col),i_row)  #单元格编号
            if i_row == 1 :
#                print("Modify column %s width from %d to %d" % (n, ws2.column_dimensions[c].width ,ws.column_dimensions[c].width))
                 ws2.column_dimensions[get_column_letter(i_col)].width = ws.column_dimensions[get_column_letter(i_col)].width
            try:
                #getattr(ws.cell(row=i_row, column=c), "value" )
                cell1=ws[i]    #获取data单元格数据
                ws2[i].value=cell1.value    #赋值到ws2单元格
                if cell1.has_style: #拷贝格式
                    ws2[i].font = copy(cell1.font)
                    ws2[i].border = copy(cell1.border)
                    ws2[i].fill = copy(cell1.fill)
                    ws2[i].number_format = copy(cell1.number_format)
                    ws2[i].protection = copy(cell1.protection)
                    ws2[i].alignment = copy(cell1.alignment)
            except AttributeError as e:
                print("cell(%s) is %s" % (i,e))
                continue

def copy_sheet(file_scr_path,src_sheet_label,file_tag_path,tag_sheet_label):
    '''在路劲file_path所指的文件中，读取src_sheet，拷贝为新的tag_sheet。'''
    print('Start copy file {} sheet {} to file {} sheet {}'.format(file_scr_path,src_sheet_label,file_tag_path,tag_sheet_label))

    wb = load_workbook(file_scr_path) #载入xlsx文件

    ws = wb[src_sheet_label] #打开源sheet页

    if os.path.exists(file_tag_path):
    	if file_scr_path == file_tag_path:
    		wb2 = wb
    	else:
    		wb2 = load_workbook(file_tag_path)
    else:
    	wb2 = Workbook()

    if tag_sheet_label in wb2.sheetnames:
    	ws2 = wb2[tag_sheet_label]
    else:
    	ws2 = wb2.create_sheet(tag_sheet_label)

    copy_sheet_simple(ws,ws2)

    if file_scr_path != file_tag_path:
    	wb.close()
    wb2.save(file_tag_path)
    wb2.close()


if __name__ == "__main__":
    path = 'F:\\mytools\\data\\一卡通账号参考.xlsx'
    #copy_sheet(path,'Sheet',path,'tag1')
    copy_sheet(path,'Sheet','F:\\mytools\\data\\一卡通账号参考2.xlsx','Sheet')
